Introduction

E-Commerce platforms has been growing in popularity as by leveraging on technology, goods and services can reach out to more consumers both domestically and internationally and thus facilitates business development. The following analysis is on a one year data collected from an UK based E-commerce platform to identify trends, analyze key metrics and extract useful insights that supports the formation of strategies for the platforms future growth.

1. Identifying the sales trend over the course of one year. (December 2018 - December 2019)

#Date Formatting 
Completed_Transactions <- Completed_Transactions %>% 
  mutate(Date = as.Date(Date, format = '%m/%d/%Y')) %>% arrange(Date)

# Dataset Preparation for Year Month
Sales_Trend <- Completed_Transactions %>% 
  group_by(yearmonth(Date)) %>% 
  summarise(Total_Sales = n()) %>% 
  rename("Year_Month" = "yearmonth(Date)")

# Obtaining the last 
Last_Date <- Completed_Transactions %>% filter(Date >= "2019-12-01" & Date <= "2019-12-31") %>% tail(1)

# Sales Trend Visualisation
ggplotly(Sales_Trend %>% ggplot(aes(x = Year_Month,
                           y = Total_Sales)) +
  geom_line() + 
  ggtitle("Sales Trend December 2018 - December 2019") +
  xlab("Date"))

The line chart below shows that there is an overall positive trend in the sales over the one year time frame, however, its worth to take not that the massive drop in sales during December 2019 was because the last data available was on 2019-12-09. Thus, it does not reflect the actual performance during December 2019.

2. What are the most frequently purchased products?

# Identifying the top 3 most purchased products
Top3_Products <- Completed_Transactions %>% group_by(ProductName) %>% 
  tally() %>% 
  rename("No_Times_Purchased" = 'n') %>% 
  arrange(-No_Times_Purchased) %>% 
  head(3)

# Bar Chart showing the top 3 products sold from December 2018 to December 2019 
ggplotly(ggplot(Top3_Products, aes(x = ProductName, y = No_Times_Purchased, fill = ProductName)) + 
  geom_bar(stat="identity") +
  ggtitle("Top 3 Products Purchased December 2018 to December 2019") +
  theme(axis.text.x = element_blank()))

Based on the analysis, the top 3 products being purchased in the year are Cream Hanging Heart T-Light Holder, Jumbo Bag Red Retrospot, Regency Cakestand 3 Tier. The sales volume of the top 3 products are of similar quantity.

2b. Does the purchase patterns for the top 3 products follow a certain pattern? Was it a one-off sale or a regular sale?

# Identifying the top 3 products sold
Top3_Products_Info <- Completed_Transactions %>% 
  filter(ProductName == Top3_Products$ProductName) %>% 
  group_by(ProductName, yearmonth(Date)) %>%
  rename("Year_Month" = "yearmonth(Date)") %>% 
  summarise(Total_Sales = n()) %>% 
  arrange(ProductName) 

# Visualizing the top 3 products sales 
ggplotly(ggplot(Top3_Products_Info, aes(x = Year_Month, 
                               y = Total_Sales,
                               group = ProductName,
                               color = ProductName)) +
  geom_line() + 
  ggtitle("Top 3 Products Sales Trend December 2018 - December 2019"))

It seems that there are some similar short term patterns between sales trend of the top 3 products, However, these patterns does not remain consistent over the year. Interestingly, it does look similar to the general sales trend of the year in the e-commerce platform. However, this may be due to random chances.

3. How much products are being purchased by customers in each transactions?

3 different Key Performance Indicators will be used to measure the value, quantity and frequency of transactions from the top performing countries

Top 5 Average Order Value Countries (KPI 1)

# Average Order Value (Total Revenues  Divide By Distinct Transactions)

# Added a total revenue column to the dataset
Completed_Transactions$Total_Revenue <- (Completed_Transactions$Quantity * Completed_Transactions$Price)

# Identifying the average order value of the top 5 countries
Top_Average_Order_Value <- Completed_Transactions %>% 
  group_by(Country) %>% 
  summarise(Average_Order_Value =    sum(Total_Revenue)/n_distinct(TransactionNo)) %>% 
  arrange(-Average_Order_Value) %>% 
  head(5)

Top_Average_Order_Value_Fig <- plot_ly(type='pie', labels=Top_Average_Order_Value$Country, 
                                       values= Top_Average_Order_Value$Average_Order_Value, 
                                       textinfo='label',
                                       insidetextorientation='centre')
Top_Average_Order_Value_Fig

Average Order Value measures the average value from each transaction performed and Netherlands has the highest Average Order Value which may indicate strong purchasing power from each purchase but the consumer base may not be very large.

Top 5 Average Order Quantity Countries (KPI 2)

# Identifying the average order quantity of the top 5 countries
Top_Average_Order_Quantity <- Completed_Transactions %>% 
  group_by(Country) %>% 
  summarise(Average_Order_Size = sum(Quantity)/n_distinct(TransactionNo)) %>% 
  arrange(-Average_Order_Size) %>% 
  head(5) 

Top_Average_Order_Quantity_Fig <- plot_ly(type = "pie", 
                                          labels = Top_Average_Order_Quantity$Country, 
                                          values = Top_Average_Order_Quantity$Average_Order_Size,
                                          textinfo = 'label', 
                                          insidetextorientation='centre')
Top_Average_Order_Quantity_Fig

Average Order Quantity measures the average quantity of products purchased in each transaction. The top performing countries based on average order value is identical to the average order quantity which indicates that the sales volume may be the main driver of the sales revenue in each country. Moreover, these countries may be potential markets that the E-Commerce platform can profit from.

Top 10 Countries Purchase Frequency (KPI 3)

# Identifying the Purchase Frequency value for the top 5 country
Purchase_Frequency_Breakdown <- Completed_Transactions %>% 
  group_by(Country) %>% 
  summarise(Purchase_Frequency = n_distinct(TransactionNo)/n_distinct(CustomerNo)) %>% 
  arrange(-Purchase_Frequency) %>% 
  count(Purchase_Frequency > 1.00) 


# Table showing the Purchase Frequency Breakdown
Purchase_Frequency_Breakdown %>% rename("No of Country" = "n") %>% 
  kable(caption = "Purchase Frequency Breakdown") %>% 
  kable_styling(full_width = F)

Purchase Frequency Breakdown
Purchase_Frequency > 1	No of Country
FALSE	7
TRUE	31

# Table showing the Purchase Frequency of the top 5 countries
Purchase_Frequency_kable <- Completed_Transactions %>% 
  group_by(Country) %>% 
  summarise(Purchase_Frequency = n_distinct(TransactionNo)/n_distinct(CustomerNo)) %>% 
  arrange(-Purchase_Frequency) %>% 
  head(5) %>% 
  kable(caption = "Top 5 Purchase Frequency Countries") %>% 
  kable_styling(full_width = F)
Purchase_Frequency_kable

Top 5 Purchase Frequency Countries
Country	Purchase_Frequency
EIRE	21.307692
Netherlands	10.444444
Iceland	8.000000
Australia	6.888889
Germany	4.978022

Out of the 38 countries that have access to this e-commerce platform, on average consumers from 31 countries have purchase frequency higher than 1, meaning consumers from majority of the countries found it useful and convenient to shop on the platform. Moreover, its worth pointing out the outstanding purchase frequency from EIRE which may be a potential target market for the e-commerce platform as consumers has high repeating purchase frequency.

4. What are the most profitable segment customers?

# Top 3 Countries based on Sales Volume
Top3_Sales_Country <- Completed_Transactions %>% 
  group_by(Country) %>% 
  summarise(Total_Sales = n_distinct(TransactionNo)) %>% 
  arrange(-Total_Sales) %>% 
  head(3)


# Total Revenue based on countries 
Top5_Revenue_Country <- Completed_Transactions %>% 
  group_by(Country) %>% 
  summarise(Total_Revenue_Dollars = sum(Price * Quantity)) %>% 
  arrange(-Total_Revenue_Dollars) %>% 
  head(3)

# Visualizing the top 3 sales volume countries 
Top3_Sales_Country_Graph <- Top3_Sales_Country %>% ggplot(aes(x= Country, 
                                  y = Total_Sales,
                                  fill = Country)) +
  geom_bar(stat ="identity") +
  ggtitle("Top 3 Sales Volume Country")

# Visualizing the top 3 sales revenue countries
Top3_Revenue_Country_Graph <- Top5_Revenue_Country %>% ggplot(aes(x= Country, 
                                  y = Total_Revenue_Dollars,
                                  fill = Country)) +
  geom_bar(stat ="identity") +
  ggtitle("Top 3 Sales Revenue Country")


Top3_Sales_Country_Graph

Top3_Revenue_Country_Graph

Based on the analysis, UK is the country with the highest sales revenue and volume which is expected as the B2C is based in UK, thus it would be more widely known by people in UK. An interesting finding is that high sales volume is not necessarily correlated with sales revenue. As it can be shown that the Top 2 and 3 countries for sales revenue and volume are different. This finding provides the E-Commerce business insights on that different strategy is needed to raise revenue and volume in different countries.

5. Based on your findings, what strategy would you recommend to the business to gain more profit?

Based on the analysis, the domestic market (UK) dominates extensively in terms of sales volume and sales revenue while the top 2 and top 3 markets in terms of sales volume and sales revenue are quite far behind. However, based on the chosen KPI’s, entering international markets to take advantage of strong customer base and satisfaction to gain profits may be a beneficial option as shown by the top 5 countries listed under each KPIs. Moreover, the e-commerce platform should prioritize annual events (Black Friday, Cyber Monday and etc) and try to provide attractive offers that are tailored to each target market’s needs and wants to stay competitive in the market competitions. However, having access to more data would facilitate better analysis and thus better basis for strategy to gain more profits.

Exploratory Data Analysis on an UK E-Commerce Platform

Wilson Victor Susanto